package com.futvan.z.framework.common.service;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Service;
import org.springframework.web.servlet.ModelAndView;

import com.futvan.z.framework.common.bean.Code;
import com.futvan.z.framework.common.bean.MenuTree;
import com.futvan.z.framework.common.bean.Result;
import com.futvan.z.system.zform.z_form_table;
import com.futvan.z.system.zform.z_form_table_column;
import com.futvan.z.framework.core.SuperService;
import com.futvan.z.framework.core.z;
import com.futvan.z.framework.util.BeanUtil;
import com.futvan.z.framework.util.DateUtil;
import com.futvan.z.framework.util.JsonUtil;
import com.futvan.z.framework.util.MathUtil;
import com.futvan.z.framework.util.StringUtil;
import com.futvan.z.system.zcode.z_code;
import com.futvan.z.system.zcode.z_code_detail;
import com.futvan.z.system.zdb.z_db;
import com.futvan.z.system.zreport.z_report;
import com.futvan.z.system.zreport.z_report_column;
import com.futvan.z.system.zuser.z_user;
import com.futvan.z.system.zworkjob.z_workjob;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
@Service
public class CommonService extends SuperService{


	/**
	 * 获取已发送有效期内的手机验证码
	 * @param tel
	 * @return
	 */
	public String getTelCode(String tel) {
		String tcode = "";
		if(z.isNotNull(tel)) {
			List<HashMap<String,String>> vcodelist = sqlSession.selectList("select", "SELECT * FROM z_vcode WHERE  tel = '"+tel+"' AND endtime > NOW() order by endtime desc");
			if(z.isNotNull(vcodelist) && vcodelist.size()>0) {
				String db_code = vcodelist.get(0).get("vcode");
				if(z.isNotNull(db_code)) {
					tcode = db_code;
				}
			}
		}
		return tcode;
	}


	/**
	 * 列表页面新增方法
	 * @param view_name
	 * @param bean
	 * @return
	 */
	public ModelAndView add(String view_name,HashMap<String,String> bean,HttpServletRequest request) {
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);
		ModelAndView model = new ModelAndView();
		model.setViewName(view_name);
		model.addObject("bean", bean);
		return model;
	}

	/**
	 * 根据字段Id获取Z5列表页面数据
	 * @param columnId
	 * @return
	 * @throws Exception 
	 */
	public ModelAndView getZ5List(HashMap<String,String> bean) throws Exception {
		ModelAndView model = new ModelAndView("common/form/z5list");
		String TableColunmId = bean.get("TableColunmId");
		String ReturnKeyColumnId = bean.get("ReturnKeyColumnId");
		String ReturnValueColumnId = bean.get("ReturnValueColumnId");
		String Z5QueryParameters = bean.get("Z5QueryParameters");
		String isReport = bean.get("isReport");//是否报表页面调用，1为报表页面
		if(z.isNotNull(TableColunmId)) {
			//关联信息
			String z5_table = "";
			String ColumnId = "";
			String z5_key = "";
			String z5_value = "";

			//获取Z5关联信息
			if(!"1".equals(isReport)) {
				z_form_table_column column = z.columns.get(TableColunmId);
				if(column!=null) {
					z5_table = column.getZ5_table();
					ColumnId = column.getColumn_id();
					z5_key = column.getZ5_key();
					z5_value = column.getZ5_value();
				}else {
					z.Exception("未查询到表单【"+TableColunmId+"】字段关联信息");
				}
			}else {
				z_report_column column = z.reportColumns.get(TableColunmId);
				if(column!=null) {
					z5_table = column.getZ5_table();
					ColumnId = column.getColumn_id();
					z5_key = column.getZ5_key();
					z5_value = column.getZ5_value();
				}else {
					z.Exception("未查询到报表【"+TableColunmId+"】字段关联信息");
				}
			}

			if(z.isNotNull(z5_table) && z.isNotNull(ColumnId)) {
				//设置查询条件
				HashMap<String,String> querybean = new HashMap<String, String>();
				querybean.put("tableId", z5_table);
				querybean.put("ColumnId", ColumnId);
				querybean.put("Z5QueryParameters", Z5QueryParameters);

				//判读是否有其它参数  其它参数格式
				String OtherParameters = bean.get("OtherParameters");
				if(z.isNotNull(OtherParameters)) {
					List<HashMap> opList = JsonUtil.jsonToList(OtherParameters, HashMap.class);
					for (HashMap map : opList) {
						querybean.put(String.valueOf(map.get("id")), String.valueOf(map.get("value")));
					}
				}

				//排序参数
				if(z.isNotNull(bean.get("orderby")) && z.isNotNull(bean.get("orderby_pattern"))) {
					querybean.put("orderby", bean.get("orderby"));
					querybean.put("orderby_pattern", bean.get("orderby_pattern"));
				}

				//分页参数
				if(z.isNotNull(bean.get("pagenum")) && z.isNotNull(bean.get("rowcount"))) {
					querybean.put("pagenum", bean.get("pagenum"));
					querybean.put("rowcount", bean.get("rowcount"));
				}else {
					querybean.put("pagenum", "1");
					querybean.put("rowcount", z.sp.get("rowcount"));
				}

				//执行查询
				model.addObject("list", selectList(querybean));

				//设置返回bean
				querybean.put("z5_key", z5_key);
				querybean.put("z5_value", z5_value);
				querybean.put("TableColunmId", TableColunmId);
				querybean.put("ReturnKeyColumnId", ReturnKeyColumnId);
				querybean.put("ReturnValueColumnId", ReturnValueColumnId);
				model.addObject("querybean", querybean);
			}else {
				z.Exception("关联信息中【关联表】或【关联字段】为空，无法查询 ");
			}
		}else {
			z.Exception("关联信息为空 ");
		}
		return model;
	}

	public String getSystemMenuTree(String userId,HttpServletRequest request){
		List<MenuTree> mtList = new ArrayList<MenuTree>();
		//获取所有顶级菜单
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct ");
		sql.append("  zm.zid, ");
		sql.append("  zm.seq zmenusql, ");
		sql.append("  zm.name text, ");
		sql.append("  zm.isopen state, ");
		sql.append("  zm.menu_icon iconCls, ");
		sql.append("  zm.url url ");
		sql.append("FROM z_menu zm ");
		//判读，如是档是管理员，添加角色权限过滤
		String sa = z.sp.get("super_user");
		if(!sa.equals(userId)) {
			sql.append("LEFT JOIN z_role_menu zrm ON zm.zid = zrm.menuid ");
			sql.append("LEFT JOIN z_role_user zru ON zrm.pid = zru.pid ");
			sql.append("WHERE (zm.parentid is null or zm.parentid = '') and zm.is_hidden = 0 and zru.userid = '"+userId+"' ");
		}else {
			sql.append("WHERE (zm.parentid is null or zm.parentid = '') and zm.is_hidden = 0  ");
		}
		sql.append("ORDER BY zm.seq ");
		List<HashMap<String,String>> mt_listmap = selectList(sql.toString());
		//遍历所有顶级菜单
		for (HashMap<String, String> mt_map : mt_listmap) {
			MenuTree mt = BeanUtil.MapToBean(mt_map, MenuTree.class);
			//获取子菜单
			getMenuTreeChildren(mt,userId);
			mtList.add(mt);
		}
		String json = JsonUtil.listToJson(mtList);
		return json;
	}

	/**
	 * 获取子菜单
	 * @param mt
	 */
	private void getMenuTreeChildren(MenuTree mt,String userId) {
		//获取子菜单菜单
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct ");
		sql.append("  zm.zid, ");
		sql.append("  zm.seq zmenuseq, ");
		sql.append("  zm.name text, ");
		sql.append("  zm.isopen state, ");
		sql.append("  zm.menu_icon iconCls, ");
		sql.append("  zm.url url ");
		sql.append("FROM z_menu zm ");
		//判读，如是档是管理员，添加角色权限过滤
		String sa = z.sp.get("super_user");
		if(!sa.equals(userId)) {
			sql.append("LEFT JOIN z_role_menu zrm ON zm.zid = zrm.menuid ");
			sql.append("LEFT JOIN z_role_user zru ON zrm.pid = zru.pid ");
			sql.append("WHERE zm.parentid = '"+mt.getZid()+"' and zm.is_hidden = 0 and zru.userid = '"+userId+"' ");
		}else {
			sql.append("WHERE zm.parentid = '"+mt.getZid()+"' and zm.is_hidden = 0  ");
		}
		sql.append("ORDER BY zm.seq ");
		List<HashMap<String,String>> mt_listmap = selectList(sql.toString());
		//遍历所有顶级菜单
		for (HashMap<String, String> mt_map : mt_listmap) {
			MenuTree c_mt = BeanUtil.MapToBean(mt_map, MenuTree.class);
			//获取子菜单
			getMenuTreeChildren(c_mt,userId);
			List<MenuTree> childrenMenuTreeList = mt.getChildren();
			if(z.isNull(childrenMenuTreeList)) {
				childrenMenuTreeList = new ArrayList<MenuTree>();
				childrenMenuTreeList.add(c_mt);
				mt.setChildren(childrenMenuTreeList);
			}else {
				mt.getChildren().add(c_mt);
			}
		}
	}



	/**
	 * 共用列表页面查询方法
	 * @param bean
	 * @param string
	 * @return
	 * @throws Exception 
	 */
	public ModelAndView list(HashMap<String,String> bean,String viewName,HttpServletRequest request) throws Exception {
		ModelAndView model = new ModelAndView(viewName);
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);

		if(z.isNull(bean.get("pagenum")) || z.isNull(bean.get("rowcount"))) {
			bean.put("pagenum", "1");

			//获取表的列表默认显示行数
			z_form_table t = z.tables.get(bean.get("tableId"));
			if(z.isNotNull(t) && z.isNotNull(t.getDefault_rowcount())) {
				bean.put("rowcount", t.getDefault_rowcount());
			}else {
				bean.put("rowcount", z.sp.get("rowcount"));
			}
		}
		model.addObject("list", selectList(bean));
		bean.put("queryinfolist", getQueryInfoList(bean));//常用用户该表单查询条件
		model.addObject("bean", bean);
		return model;
	}

	public ModelAndView rlist(HashMap<String,String> bean,String viewName,HttpServletRequest request) throws Exception {
		ModelAndView model = new ModelAndView(viewName);
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);

		z_report r = z.reports.get(bean.get("zid"));//根据报表ID获取报表相关参数
		if(z.isNotNull(r)) {
			//如果SQL不为空
			if(z.isNotNull(r.getSqlinfo())) {

				//执行SQL-获取当前页数据
				model.addObject("list",getRList(bean,r));

				//执行SQL-获取分页相关信息
				getRlistCount(bean,r);

				bean.put("queryinfolist", getQueryInfoListR(r,bean.get("session_userid")));//常用用户该表单查询条件

				//设置返回bean
				model.addObject("bean", bean);



			}else {
				throw new Exception("rlist error sql is null");
			}
		}else {
			throw new Exception("报表参数为空");
		}
		return model;
	}

	public List<HashMap<String,String>> selectList(HashMap<String,String> bean) throws Exception{
		return super.selectList(bean);
	}
	public List<HashMap<String,Object>> selectListAll(HashMap<String,String> bean) throws Exception{
		return super.selectListAll(bean);
	}
	public List<HashMap<String,String>> SelectForSQL(String sql) {
		return selectList(sql);
	}
	public <T> T selectBean(String sqlid, Object parameter) {
		return super.selectBean(sqlid, parameter);
	}
	public String selectString(String sql){
		return super.selectString(sql);
	}
	public int selectInt(String sql){
		return super.selectInt(sql);
	}

	/**
	 * 执行SQL update
	 */
	public int UpdateForSQL(String sql) {
		return update(sql);
	}

	/**
	 * 执行SQL insert
	 */
	public int InsertForSQL(String sql) {
		return insert(sql);
	}

	/**
	 * 执行SQL delete
	 */
	public int DeleteForSQL(String sql) {
		return delete(sql);
	}

	/**
	 * 共用查询单条数据
	 * @param bean
	 * @param ViewName
	 * @return
	 * @throws Exception
	 */
	public ModelAndView edit(String ViewName,HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);
		ModelAndView model = new ModelAndView(ViewName);
		model.addObject("bean", selectMap(bean));
		model.addObject("detail", selectDetailsMap(bean));
		return model;
	}

	/**
	 * 插入数据
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result insert(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		return super.insert(bean,request);
	}

	/**
	 * 修改数据 
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result update(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		return super.update(bean,request);
	}
	/**
	 * 修改数据 
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result updateForBean(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		return super.updateForBean(bean,request);
	}

	/**
	 * 删除数据
	 * @param zids
	 * @return
	 * @throws Exception
	 */
	public Result delete(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		return super.delete(bean);
	}

	public int delete(String sql){
		return super.delete(sql);
	}

	/**
	 * 打印列表
	 * @param bean
	 * @param request
	 * @return
	 * @throws Exception 
	 */
	public Result print_list(HashMap<String, String> bean, HttpServletRequest request) throws Exception {
		Result result = new Result();
		String tableId = bean.get("tableId");
		if(z.isNotNull(tableId)) {
			String zids = bean.get("zids");
			if(z.isNotNull(zids)) {
				String ph = "";
				for (String zid : zids.split(",")) {
					HashMap<String,String> qbean = new HashMap<String, String>();
					qbean.put("zid", zid);
					qbean.put("tableId", tableId);
					HashMap<String,String> c = selectMap(qbean);
					Map<String, List<HashMap>> detail = selectDetailsMap(qbean);
					if(z.isNotNull(c)) {
						String print_html = CreatePrintListHtml(tableId,c,detail);
						if(z.isNotNull(print_html)) {
							ph = ph + print_html;
						}
					}
				}
				if(z.isNotNull(ph)) {
					result.setCode(Code.SUCCESS);
					result.setMsg("ok");
					result.setData(ph);
				}
			}else {
				result.setCode(Code.ERROR);
				result.setMsg("未选择要打印的记录");
			}
		}else {
			result.setCode(Code.ERROR);
			result.setMsg("tableId is null");
		}
		return result;
	}

	/**
	 * 生成打印列表HTML
	 * @param tableId 
	 * @param c
	 * @param detail
	 * @return
	 */
	private String CreatePrintListHtml(String tableId, HashMap<String, String> c, Map<String, List<HashMap>> detail) {
		StringBuffer out_html = new StringBuffer();
		if(z.isNotNull(c) && z.isNotNull(tableId)) {
			//获取主表对象
			z_form_table table = z.tables.get(tableId);
			out_html.append("<h6>主键："+c.get("zid")+"</h6>").append("\r\n");
			out_html.append("<form id=\"main_form\" class=\"was-validated\" style=\"padding:10px;\" enctype=\"multipart/form-data\">").append("\r\n");
			out_html.append("<div class=\"container-fluid\">").append("\r\n");
			out_html.append("<div class=\"row\">").append("\r\n");
			//遍历所有字段
			for (z_form_table_column column : table.getZ_form_table_column_list()) {
				//判读是否主键或外键
				if(!"zid".equals(column.getColumn_id()) && !"pid".equals(column.getColumn_id()))
					//判读字段是否隐藏
					if("0".equals(column.getIs_hidden()) && "0".equals(column.getIs_hidden_edit())) {//判读是否隐藏
						//创建字段
						out_html.append("<div class=\"col-"+column.getColumn_size()+"\">").append("\r\n");
						out_html.append("<fieldset class=\"form-group\">").append("\r\n");
						out_html.append("<label data-toggle=\"tooltip\" class=\"margin-bottom: 0px;"+ColumnIsNull(column.getIs_null())+"\" data-placement=\"top\" title=\""+column.getColumn_help()+"\">【"+column.getColumn_name()+"】</label>").append("\r\n");
						if("0".equals(column.getColumn_type())) {//文本
							out_html.append("<input placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" type=\"text\" class=\"form-control\" value=\""+c.get(column.getColumn_id())+"\" "+ColumnIsReadonly(column.getIs_readonly())+"/>").append("\r\n");
						}else if("1".equals(column.getColumn_type())) {//多行文本
							String textarea_height = "300";
							if(column.getTextarea_height()!=null && !"".equals(column.getTextarea_height())) {
								textarea_height = column.getTextarea_height();
							}
							out_html.append("<textarea placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" style=\"height: "+textarea_height+"px\" class=\"form-control\" "+ColumnIsReadonly(column.getIs_readonly())+">"+c.get(column.getColumn_id())+"</textarea>").append("\r\n");
						}else if("2".equals(column.getColumn_type())) {//数字
							out_html.append("<input placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" type=\"number\" class=\"form-control\" value=\""+c.get(column.getColumn_id())+"\"  "+ColumnIsReadonly(column.getIs_readonly())+"/>").append("\r\n");
						}else if("3".equals(column.getColumn_type())) {//文件
							out_html.append("<div class=\"input-group\">").append("\r\n");
							String ColumnValue = c.get(column.getColumn_id());
							out_html.append("<input placeholder=\""+column.getColumn_help()+"\"  id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" value=\""+getZ5DisplayValue(column,ColumnValue)+"\" type=\"url\" class=\"form-control marginleft1\" readonly=\"readonly\"/>").append("\r\n");
							out_html.append("<span class=\"input-group-btn btn-group btn-group-sm marginright1\">").append("\r\n");
							out_html.append("<button class=\"btn btn-light\" onclick=\"downloadFile('"+column.getColumn_id()+"')\" type=\"button\"><i class=\"fa fa-download\"></i></button>").append("\r\n");
							out_html.append("</span>").append("\r\n");
							out_html.append("</div>").append("\r\n");
						}else if("4".equals(column.getColumn_type())) {//图片
							out_html.append("<div class=\"input-group\">").append("\r\n");
							String ColumnValue = c.get(column.getColumn_id());
							out_html.append("<input placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" value=\""+getZ5DisplayValue(column,ColumnValue)+"\" type=\"url\" class=\"form-control marginleft1\" readonly=\"readonly\"/>").append("\r\n");
							out_html.append("<span class=\"input-group-btn btn-group btn-group-sm marginright1\">").append("\r\n");
							out_html.append("<button class=\"btn btn-light\" onclick=\"lookupImg('"+column.getColumn_id()+"')\" type=\"button\"><i class=\"fa fa-picture-o\"></i></button>").append("\r\n");
							out_html.append("</span>").append("\r\n");
							out_html.append("</div>").append("\r\n");
						}else if("5".equals(column.getColumn_type())) {//多选
							//获取Code
							if(!"".equals(column.getP_code_id()) && column.getP_code_id()!=null) {
								z_code code = z.code.get(column.getP_code_id());
								if(code!=null && code.getZ_code_detail_list().size()>0) {
									List<z_code_detail> delailList = code.getZ_code_detail_list();
									out_html.append("<div class=\"zcheckbox_div\">").append("\r\n");
									String value = c.get(column.getColumn_id());
									out_html.append("<input id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" type=\"hidden\"  value=\""+value+"\"/>").append("\r\n");
									for (z_code_detail code_d : delailList) {
										//如果字段值与Key相同，选中状态
										boolean ic = isChecked(value,code_d.getZ_key());
										String ischecked = "";
										if(ic) {
											ischecked = "checked=\"checked\"";
										}
										//如果只读换成隐藏域
										String type = "checkbox";
										String hidden_ico = "";
										if("1".equals(column.getIs_readonly())) {
											type = "hidden";
											if(z.isNotNull(ischecked)) {
												hidden_ico = "<i class=\"fa fa-check-square-o\"></i> ";
											}else {
												hidden_ico = "<i class=\"fa fa-square-o\"></i> ";
											}
										}
										out_html.append("<div class=\""+GetArrangementDirectionCss(column.getArrangement_direction())+"\"><input class=\"zcheckbox\" onchange=\"getCheckedValue('"+column.getColumn_id()+"_id','"+column.getColumn_id()+"_checked_id')\" type=\""+type+"\" "+ischecked+" id=\""+column.getColumn_id()+"_checked_id\" value=\""+code_d.getZ_key()+"\" "+ColumnIsReadonly(column.getIs_readonly())+">"+hidden_ico+code_d.getZ_value()+" </div>").append("\r\n");
									}
									out_html.append("</div>").append("\r\n");
								}
							}
						}else if("6".equals(column.getColumn_type())) {//单选
							//获取Code
							if(!"".equals(column.getP_code_id()) && column.getP_code_id()!=null) {
								z_code code = z.code.get(column.getP_code_id());
								if(code!=null && code.getZ_code_detail_list().size()>0) {
									List<z_code_detail> delailList = code.getZ_code_detail_list();
									out_html.append("<div class=\"zcheckbox_div\" "+ColumnIsReadonly(column.getIs_readonly())+">").append("\r\n");
									for (z_code_detail code_d : delailList) {
										String value = c.get(column.getColumn_id());
										//如果字段值与Key相同，选中状态
										String ischecked = "";
										if(value.equals(code_d.getZ_key())) {
											ischecked = "checked=\"checked\"";
										}
										String type = "radio";
										//如果只读换成隐藏域
										String hidden_ico = "";
										if("1".equals(column.getIs_readonly())) {
											type = "hidden";
											if(z.isNotNull(ischecked)) {
												hidden_ico = "<i class=\"fa fa-dot-circle-o\"></i> ";
											}else {
												hidden_ico = "<i class=\"fa fa-circle-o\"></i> ";
											}
										}
										out_html.append("<div class=\""+GetArrangementDirectionCss(column.getArrangement_direction())+"\"><input class=\"zcheckbox\" type=\""+type+"\" "+ischecked+" name=\""+column.getColumn_id()+"\" id=\""+column.getColumn_id()+"_id\" value=\""+code_d.getZ_key()+"\">"+hidden_ico+code_d.getZ_value()+" </div>").append("\r\n");
									}
									out_html.append("</div>").append("\r\n");
								}

							}
						}else if("7".equals(column.getColumn_type())) {//下拉框
							out_html.append("<select class=\"form-control\" name=\""+column.getColumn_id()+"\" id=\""+column.getColumn_id()+"_id\" "+ColumnIsReadonly(column.getIs_readonly())+">").append("\r\n");
							out_html.append("<option value=\"\">请选择</option>").append("\r\n");
							//获取Code
							if(!"".equals(column.getP_code_id()) && column.getP_code_id()!=null) {
								z_code code = z.code.get(column.getP_code_id());
								if(code!=null && code.getZ_code_detail_list().size()>0) {
									List<z_code_detail> delailList = code.getZ_code_detail_list();
									for (z_code_detail code_d : delailList) {
										String value = c.get(column.getColumn_id());
										if(value.equals(code_d.getZ_key())) {
											out_html.append("<option value=\""+code_d.getZ_key()+"\" selected=\"selected\">"+code_d.getZ_value()+"</option>").append("\r\n");
										}else {
											out_html.append("<option value=\""+code_d.getZ_key()+"\" >"+code_d.getZ_value()+"</option>").append("\r\n");
										}
									}
								}
							}
							out_html.append("</select>").append("\r\n");
						}else if("8".equals(column.getColumn_type())) {//Z5
							out_html.append("<div class=\"input-group\">").append("\r\n");
							String ColumnValue = c.get(column.getColumn_id());
							String SelectDataOnClick = "onclick=\"Z5list('"+table.getTable_id()+"_"+column.getColumn_id()+"','"+column.getColumn_id()+"_id','"+column.getColumn_id()+"_display',0)\"";
							//如果字段是只读，删除输入框点击事件
							if("1".equals(column.getIs_readonly())) {
								SelectDataOnClick = "";
							}
							out_html.append("<input id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" type=\"hidden\"  value=\""+ColumnValue+"\"/>").append("\r\n");
							out_html.append("<input  placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_display\" "+SelectDataOnClick+" name=\""+column.getColumn_id()+"_display\" value=\""+getZ5DisplayValue(column,ColumnValue)+"\" type=\"text\" class=\"form-control marginleft1\" "+" readonly />").append("\r\n");
							out_html.append("<span class=\"input-group-prepend marginright1\">").append("\r\n");
							out_html.append("</span>").append("\r\n");
							out_html.append("</div>").append("\r\n");

						}else if("9".equals(column.getColumn_type())) {//日期
							if("1".equals(column.getIs_readonly())) {
								//只读
								out_html.append("<input placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" type=\"text\" class=\"form-control\" value=\""+c.get(column.getColumn_id())+"\" "+ColumnIsReadonly(column.getIs_readonly())+"/>").append("\r\n");
							}else {
								//可以
								out_html.append("<input  placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" type=\"text\" class=\"Wdate form-control\" value=\""+c.get(column.getColumn_id())+"\" onFocus=\"WdatePicker({isShowClear:true,readOnly:true,dateFmt:'yyyy-MM-dd'})\" "+ColumnIsReadonly(column.getIs_readonly())+"/>").append("\r\n");
							}

						}else if("10".equals(column.getColumn_type())) {//日期时间
							if("1".equals(column.getIs_readonly())) {
								//只读
								out_html.append("<input placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" type=\"text\" class=\"form-control\" value=\""+c.get(column.getColumn_id())+"\" "+ColumnIsReadonly(column.getIs_readonly())+"/>").append("\r\n");
							}else {
								//可以
								out_html.append("<input  placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" type=\"text\" class=\"Wdate form-control\" value=\""+c.get(column.getColumn_id())+"\" onFocus=\"WdatePicker({isShowClear:true,readOnly:true,dateFmt:'yyyy-MM-dd HH:mm:ss'})\" "+ColumnIsReadonly(column.getIs_readonly())+"/>").append("\r\n");
							}

						}else if("11".equals(column.getColumn_type())) {//HTML输入框
							String textarea_height = "300";
							if(column.getTextarea_height()!=null && !"".equals(column.getTextarea_height())) {
								textarea_height = column.getTextarea_height();
							}
							out_html.append("<textarea  placeholder=\""+column.getColumn_help()+"\" id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" style=\"height: "+textarea_height+"px\" "+ColumnIsReadonly(column.getIs_readonly())+">"+c.get(column.getColumn_id())+"</textarea>").append("\r\n");
							out_html.append("<script type=\"text/javascript\">InitHTMLColumn('"+column.getColumn_id()+"_id');</script>").append("\r\n");
						}else if("12".equals(column.getColumn_type())) {//源码输入框
							String textarea_height = "300";
							if(column.getTextarea_height()!=null && !"".equals(column.getTextarea_height())) {
								textarea_height = column.getTextarea_height();
							}
							out_html.append("<textarea  placeholder=\""+column.getColumn_help()+"\"  id=\""+column.getColumn_id()+"_id\" name=\""+column.getColumn_id()+"\" style=\"height: "+textarea_height+"px\" "+ColumnIsReadonly(column.getIs_readonly())+">"+c.get(column.getColumn_id())+"</textarea>").append("\r\n");
							out_html.append("<script type=\"text/javascript\">InitCodeColumn('"+column.getColumn_id()+"_id','"+column.getMode_type()+"','"+textarea_height+"');</script>").append("\r\n");
						}
						out_html.append("</fieldset>").append("\r\n");
						out_html.append("</div>").append("\r\n");

						//添加换行
						if("1".equals(column.getIsbr())) {
							out_html.append("<div class=\"col-12\"></div>").append("\r\n");
						}

						//添加分割线
						if("1".equals(column.getIshr())) {
							out_html.append("<div class=\"col-12\"><hr/></div>").append("\r\n");
						}

					}
			}
			out_html.append("</div>").append("\r\n");
			out_html.append("</div>").append("\r\n");
			out_html.append("</form>").append("\r\n");



			//如果有明细记录，添加明细记录
			List<z_form_table> dtableList = table.getZ_form_table_detail_list();
			for (z_form_table dtable : dtableList) {
				out_html.append("<h2>"+dtable.getTable_title()+"</h2><table class='table table-bordered'><thead><tr>").append("\r\n");
				//生成表格头
				List<z_form_table_column> dtable_column_list = dtable.getZ_form_table_column_list();
				for (z_form_table_column dc : dtable_column_list) {
					if(!"zid".equals(dc.getColumn_id()) && !"pid".equals(dc.getColumn_id())) {
						//判读字段是否隐藏
						if("0".equals(dc.getIs_hidden()) && "0".equals(dc.getIs_hidden_edit())) {//判读是否隐藏
							out_html.append("<th scope='col'>"+dc.getColumn_name()+"</th>").append("\r\n");
						}
					}


				}
				out_html.append("</tr></thead>").append("\r\n");
				out_html.append("<tbody>").append("\r\n");
				//生成表格数据行
				List<HashMap> detailData = detail.get(dtable.getTable_id());
				for (HashMap data : detailData) {
					out_html.append("<tr>").append("\r\n");
					for (z_form_table_column dc : dtable_column_list) {
						if(!"zid".equals(dc.getColumn_id()) && !"pid".equals(dc.getColumn_id())) {
							//判读字段是否隐藏
							if("0".equals(dc.getIs_hidden()) && "0".equals(dc.getIs_hidden_edit())) {//判读是否隐藏

								if("0".equals(dc.getColumn_type())) {//文本
									out_html.append("<td>"+data.get(dc.getColumn_id())+"</td>").append("\r\n");
								}else if("1".equals(dc.getColumn_type())) {//多行文本
									out_html.append("<td>"+data.get(dc.getColumn_id())+"</td>").append("\r\n");
								}else if("2".equals(dc.getColumn_type())) {//数字
									out_html.append("<td>"+data.get(dc.getColumn_id())+"</td>").append("\r\n");
								}else if("3".equals(dc.getColumn_type())) {//文件
									out_html.append("<td>"+data.get(dc.getColumn_id())+"</td>").append("\r\n");
								}else if("4".equals(dc.getColumn_type())) {//图片
									out_html.append("<td>"+data.get(dc.getColumn_id())+"</td>").append("\r\n");
								}else if("5".equals(dc.getColumn_type())) {//多选
									String CheckedValue = CheckedValue(dc,StringUtil.toString(data.get(dc.getColumn_id())));
									out_html.append("<td>"+printTd(CheckedValue,dc,data)+"</td>").append("\r\n");
								}else if("6".equals(dc.getColumn_type())) {//单选
									String cv = z.codeValue.get(dc.getP_code_id()+"_"+data.get(dc.getColumn_id()));
									if("".equals(cv) || cv==null) {
										cv = StringUtil.toString(data.get(dc.getColumn_id()));
									}
									out_html.append("<td>"+  CodeValueColor(dc.getP_code_id()+"_"+data.get(dc.getColumn_id()),printTd(cv,dc,data))+"</td>").append("\r\n");
								}else if("7".equals(dc.getColumn_type())) {//下拉框
									String cv = z.codeValue.get(dc.getP_code_id()+"_"+data.get(dc.getColumn_id()));
									if("".equals(cv) || cv==null) {
										cv = StringUtil.toString(data.get(dc.getColumn_id()));
									}
									out_html.append("<td>"+  CodeValueColor(dc.getP_code_id()+"_"+data.get(dc.getColumn_id()),printTd(cv,dc,data))+"</td>").append("\r\n");
								}else if("8".equals(dc.getColumn_type())) {//Z5
									String columnValue = StringUtil.toString(data.get(dc.getColumn_id()));
									out_html.append("<td>"+printTd(getZ5DisplayValue(dc,columnValue),dc,data)+"</td>").append("\r\n");
								}else if("9".equals(dc.getColumn_type())) {//日期
									out_html.append("<td>"+printTd(DateUtil.FormatDate(data.get(dc.getColumn_id()), "yyyy-MM-dd"),dc,data)+"</td>").append("\r\n");
								}else if("10".equals(dc.getColumn_type())) {//日期时间
									out_html.append("<td>"+printTd(DateUtil.FormatDate(data.get(dc.getColumn_id()), "yyyy-MM-dd HH:mm:ss"),dc,data)+"</td>").append("\r\n");
								}else if("11".equals(dc.getColumn_type())) {//HTML输入框
									out_html.append("<td>"+data.get(dc.getColumn_id())+"</td>").append("\r\n");
								}else if("12".equals(dc.getColumn_type())) {//源码输入框
									out_html.append("<td>"+data.get(dc.getColumn_id())+"</td>").append("\r\n");
								}
							}
						}

					}
					out_html.append("</tr>").append("\r\n");
				}
				out_html.append("</tbody></table>").append("\r\n");
			}

			out_html.append("<hr>").append("\r\n");
		}
		return out_html.toString();
	}

	/**
	 *	下移或上移
	 * @param bean
	 * @param string
	 * @return
	 * @throws Exception 
	 */
	public ModelAndView MoveDownOrMoveUp(HashMap<String, String> bean) throws Exception {
		if("list".equals(bean.get("PageType"))) {
			ModelAndView model = new ModelAndView("common/form/list");
			String tableId = bean.get("tableId");


			//获取当前记录seq
			String sql1 = "SELECT * FROM "+tableId+" WHERE zid = '"+bean.get("zid")+"' ";
			HashMap bill_1 = selectMap(sql1);

			//获取当前记录的下一条记录  
			String sql2 = "SELECT * FROM "+tableId+" WHERE zid = '"+bean.get("nextzid")+"' ";
			HashMap bill_2 = selectMap(sql2.toString());

			//如果当前bill2为null 表示当前为最后一条记录
			if(bill_2 != null) {
				//执行下移操作
				String update_1 = "UPDATE "+tableId+" SET  seq = "+bill_2.get("seq")+" WHERE zid = '"+bill_1.get("zid")+"'";
				int u1count = update(update_1);
				if(u1count!=1) {
					throw new Exception("error moveDown u1count is not 1");
				}

				String update_2 = "UPDATE "+tableId+" SET  seq = "+bill_1.get("seq")+" WHERE zid = '"+bill_2.get("zid")+"'";
				int u2count = update(update_2);
				if(u2count!=1) {
					throw new Exception("error moveDown u2count is not 1");
				}

			}

			bean.put("checked_zid", bean.get("zid"));
			bean.remove("zid");

			//设置分页条件
			if(!z.isNotNull(bean.get("pagenum")) || !z.isNotNull(bean.get("rowcount"))) {
				bean.put("pagenum", "1");
				bean.put("rowcount", z.sp.get("rowcount"));
			}

			model.addObject("list", selectList(bean));
			model.addObject("bean", bean);
			return model;
		}else if("edit".equals(bean.get("PageType"))) {
			ModelAndView model = new ModelAndView("common/form/edit");
			String tableId = bean.get("tableId");

			//获取当前记录seq
			String sql1 = "SELECT * FROM "+tableId+" WHERE zid = '"+bean.get("zid")+"' ";
			HashMap bill_1 = selectMap(sql1);

			//获取当前记录的下一条记录  
			String sql2 = "SELECT * FROM "+tableId+" WHERE zid = '"+bean.get("nextzid")+"' ";
			HashMap bill_2 = selectMap(sql2.toString());

			//如果当前bill2为null 表示当前为最后一条记录
			if(bill_2 != null) {
				//执行下移操作
				String update_1 = "UPDATE "+tableId+" SET  seq = "+bill_2.get("seq")+" WHERE zid = '"+bill_1.get("zid")+"'";
				int u1count = update(update_1);
				if(u1count!=1) {
					throw new Exception("error moveDown u1count is not 1");
				}

				String update_2 = "UPDATE "+tableId+" SET  seq = "+bill_1.get("seq")+" WHERE zid = '"+bill_2.get("zid")+"'";
				int u2count = update(update_2);
				if(u2count!=1) {
					throw new Exception("error moveDown u2count is not 1");
				}

			}

			bean.put("checked_zid", bean.get("zid"));
			bean.put("checked_TableID", tableId+"_detail_table");
			bean.put("checked_TableTitle", z.tables.get(tableId).getTable_title());

			//设置ZID为主表的ZID
			bean.put("zid", String.valueOf(bill_1.get("pid")));
			//设置tableId为父表tableId
			bean.put("tableId", z.tables.get(tableId).getParent_table_id());


			model.addObject("bean", selectMap(bean));
			model.addObject("detail", selectDetailsMap(bean));
			return model;
		}else {
			throw new Exception("error PageType is null");
		}
	}

	/**
	 * 根据formId获取tableList
	 * @param formId
	 * @return
	 */
	public List<z_form_table> getTableList(String formId) {
		List<z_form_table> tableList = new ArrayList<z_form_table>();
		List<Map<String,String>> tableListMap = selectList("SELECT zft.* FROM z_form zf INNER JOIN z_form_table zft ON zf.zid = zft.pid WHERE zf.form_id = '"+formId+"'");
		for (Map<String, String> tableMap : tableListMap) {
			z_form_table table = BeanUtil.MapToBean(tableMap, z_form_table.class);
			tableList.add(table);
		}
		return tableList;
	}

	/**
	 * 生成导出excel文件流
	 * @param bean
	 * @return byte[]
	 * @throws Exception
	 */
	public byte[] getExportData(HashMap<String,String> bean) throws Exception {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		WritableWorkbook wwb = Workbook.createWorkbook(out); 
		//根据表名获取表信息
		z_form_table table = z.tables.get(bean.get("tableId"));
		if(table==null) {
			throw new Exception("生成导出excel出错，找不到Table");
		}
		//生成主Sheet
		int sheet_num = 0;
		bean.remove("pagenum");
		bean.remove("rowcount");
		List<HashMap<String,String>> listData = selectList(bean);
		CreateSheet(wwb,table,sheet_num,listData);
		//生成明细表Sheet
		for (int i = 0; i < table.getZ_form_table_detail_list().size(); i++) {
			//获取明细表
			z_form_table dTable = table.getZ_form_table_detail_list().get(i);
			//获取明细表数据
			//获取主表zid,封装成in条件
			HashMap<String,String> dbean = new HashMap<String, String>();
			dbean.put("tableId", dTable.getTable_id());
			dbean.put("otherwhere", CreatePIDS(listData));
			List<HashMap<String,String>> dListData = selectList(dbean);
			CreateSheet(wwb,dTable,sheet_num+1,dListData);
		}
		wwb.write();
		wwb.close();
		return out.toByteArray();
	}

	/**
	 * 生成导出excel文件流
	 * @param bean
	 * @return byte[]
	 * @throws Exception
	 */
	public byte[] getExportDataR(HashMap<String,String> bean) throws Exception {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		WritableWorkbook wwb = Workbook.createWorkbook(out); 

		//获取报表ID
		String zid = bean.get("zid");
		z_report r = z.reports.get(zid);
		if(!z.isNotNull(r)) {
			throw new Exception("生成导出excel出错，找不到报表参数R");
		}

		//生成主Sheet
		int sheet_num = 0;
		//封装SQL
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM ( ");
		sql.append(r.getSqlinfo());
		sql.append(" ) z_report_select  ");

		//添加过滤条件
		sql.append(" where 1=1 ");
		//添加综合查询条件
		if(!"".equals(bean.get("query_terms")) && bean.get("query_terms")!=null) {
			String query_terms_sql = CreateQueryTerms(bean.get("query_terms"));
			if(!"".equals(query_terms_sql) && query_terms_sql!=null) {
				sql.append(" and "+query_terms_sql+" ");
			}
		}
		//添加其它条件
		if(!"".equals(bean.get("otherwhere")) && bean.get("otherwhere")!=null) {
			sql.append(" and "+bean.get("otherwhere")+" ");
		}

		//添加排序条件
		if(z.isNotNull(bean.get("orderby")) && z.isNotNull(bean.get("orderby_pattern"))) {
			//如果参数对象的orderby条件不为空，使用参数对象的条件
			sql.append(" order by "+bean.get("orderby")+" "+bean.get("orderby_pattern"));
		}
		List<HashMap<String,String>> listData;
		if(z.isNull(r.getDbid())) {
			listData = selectList(sql.toString());
		}else {
			listData = selectList(z.dbs.get(r.getDbid()),sql.toString());
		}

		CreateSheetR(wwb,r,sheet_num,listData);
		wwb.write();
		wwb.close();
		return out.toByteArray();
	}

	/**
	 * 生成PID查询条件
	 * @param listData
	 * @return
	 */
	private String CreatePIDS(List<HashMap<String, String>> listData) {
		//String result = " pid in ( ";
		StringBuffer result = new StringBuffer();
		result.append(" pid in ( ");
		if(z.isNotNull(listData)) {
			for (int i = 0; i < listData.size(); i++) {
				HashMap<String,String> hr = listData.get(i);
				if(i==0) {
					result.append("'"+hr.get("zid")+"'");
				}else {
					result.append(",'"+hr.get("zid")+"'");
				}
			}
		}
		result.append(" ) ");
		return result.toString();
	}

	/**
	 * 导出Excel ---  生成sheet页面
	 * @param table
	 * @param sheet_num
	 * @throws Exception
	 */
	private void CreateSheet(WritableWorkbook wwb,z_form_table table,int sheet_num,List<HashMap<String,String>> listData) throws Exception {

		//创建工作表
		wwb.createSheet(table.getTable_title(), sheet_num);

		//获取工作表 
		WritableSheet ws = wwb.getSheet(sheet_num);

		//表头行样式
		WritableCellFormat TableHead = new WritableCellFormat();
		TableHead.setBorder(Border.ALL, BorderLineStyle.THIN);
		TableHead.setAlignment(Alignment.LEFT);
		TableHead.setBackground(Colour.GRAY_25);
		//表体数据行样式
		WritableCellFormat TableRow = new WritableCellFormat();
		TableRow.setAlignment(Alignment.LEFT);

		//获取所有字段
		List<z_form_table_column> columnArray = new ArrayList<z_form_table_column>();
		List<z_form_table_column> tableColumnList = table.getZ_form_table_column_list();
		for (int i = 0; i < tableColumnList.size(); i++) {
			z_form_table_column column = tableColumnList.get(i);
			if("0".equals(column.getIs_hidden())) {//判读是否隐藏
				if(!"1".equals(column.getIs_hidden_list())){//判读是否列表隐藏
					columnArray.add(column);
				}
			}

		}
		//添加主键外键
		columnArray.add(z.columns.get(table.getTable_id()+"_zid"));
		if(sheet_num!=0) {
			columnArray.add(z.columns.get(table.getTable_id()+"_pid"));
		}

		//生成表头行
		for (int i = 0; i < columnArray.size(); i++) {
			if(z.isNotNull(columnArray.get(i))){
				z_form_table_column column = columnArray.get(i);
				ws.addCell(new Label(i,0,column.getColumn_name(),TableHead));
			}
		}


		//生成记录行
		if(z.isNotNull(listData)) {
			for (int i = 0; i < listData.size(); i++) {
				HashMap<String,String> hr = listData.get(i);
				for (int j = 0; j < columnArray.size(); j++) {
					if(z.isNotNull(columnArray.get(j))){
						z_form_table_column column = columnArray.get(j);
						if("0".equals(column.getColumn_type())) {//文本
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("1".equals(column.getColumn_type())) {//多行文本
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("2".equals(column.getColumn_type())) {//数字
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("3".equals(column.getColumn_type())) {//文件
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("4".equals(column.getColumn_type())) {//图片
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("5".equals(column.getColumn_type())) {//多选
							String CheckedValue = CheckedValue(column,StringUtil.toString(hr.get(column.getColumn_id())));
							ws.addCell(new Label(j,i+1,CheckedValue,TableRow));
						}else if("6".equals(column.getColumn_type())) {//单选
							String cv = z.codeValue.get(column.getP_code_id()+"_"+hr.get(column.getColumn_id()));
							if("".equals(cv) || cv==null) {
								cv = StringUtil.toString(hr.get(column.getColumn_id()));
							}
							ws.addCell(new Label(j,i+1,StringUtil.toString(cv),TableRow));
						}else if("7".equals(column.getColumn_type())) {//下拉框
							String cv = z.codeValue.get(column.getP_code_id()+"_"+hr.get(column.getColumn_id()));
							if("".equals(cv) || cv==null) {
								cv = StringUtil.toString(hr.get(column.getColumn_id()));
							}
							ws.addCell(new Label(j,i+1,StringUtil.toString(cv),TableRow));
						}else if("8".equals(column.getColumn_type())) {//Z5
							String columnValue = StringUtil.toString(hr.get(column.getColumn_id()));
							ws.addCell(new Label(j,i+1,getZ5DisplayValue(column,columnValue),TableRow));
						}else if("9".equals(column.getColumn_type())) {//日期
							ws.addCell(new Label(j,i+1,DateUtil.FormatDate(hr.get(column.getColumn_id()), "yyyy-MM-dd"),TableRow));
						}else if("10".equals(column.getColumn_type())) {//日期时间
							ws.addCell(new Label(j,i+1,DateUtil.FormatDate(hr.get(column.getColumn_id()), "yyyy-MM-dd HH:mm:ss"),TableRow));
						}else if("11".equals(column.getColumn_type())) {//HTML输入框
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("12".equals(column.getColumn_type())) {//源码输入框
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}
					}
				}
			}
		}

	}

	/**
	 * 导出Excel ---  生成sheet页面
	 * @param table
	 * @param sheet_num
	 * @throws Exception
	 */
	private void CreateSheetR(WritableWorkbook wwb,z_report r,int sheet_num,List<HashMap<String,String>> listData) throws Exception {

		//创建工作表
		wwb.createSheet(r.getTitle(), sheet_num);

		//获取工作表 
		WritableSheet ws = wwb.getSheet(sheet_num);

		//表头行样式
		WritableCellFormat TableHead = new WritableCellFormat();
		TableHead.setBorder(Border.ALL, BorderLineStyle.THIN);
		TableHead.setAlignment(Alignment.LEFT);
		TableHead.setBackground(Colour.GRAY_25);
		//表体数据行样式
		WritableCellFormat TableRow = new WritableCellFormat();
		TableRow.setAlignment(Alignment.LEFT);

		//获取所有字段
		List<z_report_column> columnArray = new ArrayList<z_report_column>();
		List<z_report_column> tableColumnList = r.getZ_report_column_list();
		for (int i = 0; i < tableColumnList.size(); i++) {
			z_report_column column = tableColumnList.get(i);
			columnArray.add(column);

		}

		//生成表头行
		for (int i = 0; i < columnArray.size(); i++) {
			if(z.isNotNull(columnArray.get(i))){
				z_report_column column = columnArray.get(i);
				ws.addCell(new Label(i,0,column.getColumn_name(),TableHead));
			}
		}


		//生成记录行
		if(z.isNotNull(listData)) {
			for (int i = 0; i < listData.size(); i++) {
				HashMap<String,String> hr = listData.get(i);
				for (int j = 0; j < columnArray.size(); j++) {
					if(z.isNotNull(columnArray.get(j))){
						z_report_column column = columnArray.get(j);
						if("0".equals(column.getColumn_type())) {//文本
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("1".equals(column.getColumn_type())) {//多行文本
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("2".equals(column.getColumn_type())) {//数字
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("3".equals(column.getColumn_type())) {//文件
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("4".equals(column.getColumn_type())) {//图片
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("5".equals(column.getColumn_type())) {//多选
							String CheckedValue = CheckedValueR(column,StringUtil.toString(hr.get(column.getColumn_id())));
							ws.addCell(new Label(j,i+1,CheckedValue,TableRow));
						}else if("6".equals(column.getColumn_type())) {//单选
							String cv = z.codeValue.get(column.getP_code_id()+"_"+hr.get(column.getColumn_id()));
							if("".equals(cv) || cv==null) {

								cv = StringUtil.toString(hr.get(column.getColumn_id()));
							}
							ws.addCell(new Label(j,i+1,StringUtil.toString(cv),TableRow));
						}else if("7".equals(column.getColumn_type())) {//下拉框
							String cv = z.codeValue.get(column.getP_code_id()+"_"+hr.get(column.getColumn_id()));
							if("".equals(cv) || cv==null) {
								cv = StringUtil.toString(hr.get(column.getColumn_id()));
							}
							ws.addCell(new Label(j,i+1,StringUtil.toString(cv),TableRow));
						}else if("8".equals(column.getColumn_type())) {//Z5
							String columnValue = StringUtil.toString(hr.get(column.getColumn_id()));
							ws.addCell(new Label(j,i+1,getZ5DisplayValueR(column,columnValue),TableRow));
						}else if("9".equals(column.getColumn_type())) {//日期
							ws.addCell(new Label(j,i+1,DateUtil.FormatDate(hr.get(column.getColumn_id()), "yyyy-MM-dd"),TableRow));
						}else if("10".equals(column.getColumn_type())) {//日期时间
							ws.addCell(new Label(j,i+1,DateUtil.FormatDate(hr.get(column.getColumn_id()), "yyyy-MM-dd HH:mm:ss"),TableRow));
						}else if("11".equals(column.getColumn_type())) {//HTML输入框
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("12".equals(column.getColumn_type())) {//源码输入框
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}
					}
				}
			}
		}

	}

	/**
	 * 执行SQL-获取分页相关信息
	 * @param bean
	 * @param r
	 */
	private void getRlistCount(HashMap<String, String> bean, z_report r) {
		//封装SQL
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(*) FROM ( ");
		//变量替换 
		sql.append(StringUtil.parseExpression(r.getSqlinfo(), bean)   );
		sql.append(" ) z_report_select  ");

		//添加过滤条件
		sql.append(" where 1=1 ");
		//添加综合查询条件
		if(!"".equals(bean.get("query_terms")) && bean.get("query_terms")!=null) {
			String query_terms_sql = CreateQueryTerms(bean.get("query_terms"));
			if(!"".equals(query_terms_sql) && query_terms_sql!=null) {
				sql.append(" and "+query_terms_sql+" ");
			}
		}
		//添加其它条件
		if(!"".equals(bean.get("otherwhere")) && bean.get("otherwhere")!=null) {
			sql.append(" and "+bean.get("otherwhere")+" ");
		}

		//添加排序条件
		if(z.isNotNull(bean.get("orderby")) && z.isNotNull(bean.get("orderby_pattern"))) {
			//如果参数对象的orderby条件不为空，使用参数对象的条件
			sql.append(" order by "+bean.get("orderby")+" "+bean.get("orderby_pattern"));
		}
		//设置返回记录总数
		int datacount = 0;
		if(z.isNull(r.getDbid()) || "z".equals(r.getDbid())) {
			datacount = selectInt(sql.toString());
		}else {
			datacount = selectInt(z.dbs.get(r.getDbid()),sql.toString());
		}

		bean.put("datacount", String.valueOf(datacount));

		//设置总页数
		int rowcount = new Integer(z.sp.get("rowcount"));
		if(z.isNotNull(bean.get("rowcount"))) {
			rowcount = new Integer(bean.get("rowcount"));
		}
		bean.put("pagecount", MathUtil.getPageCount(datacount,rowcount));
	}

	/**
	 * 执行SQL-获取当前页数据
	 * @param bean
	 * @param r
	 * @return
	 */
	private List<HashMap<String,String>> getRList(HashMap<String, String> bean,z_report r) {
		//封装SQL
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM ( ");
		//变量替换 
		sql.append(StringUtil.parseExpression(r.getSqlinfo(), bean)   );
		sql.append(" ) z_report_select  ");

		//添加过滤条件
		sql.append(" where 1=1 ");

		for(z_report_column col :r.getZ_report_column_list()) {

		}


		//添加条件
		for (String key : bean.keySet()) {
			String name = key;//获取参数名
			if(z.reportColumns.get(r.getReportid()+"_"+key)!=null || z.reportColumns.get(r.getReportid()+"_"+key.replace("_from", ""))!=null) {

				if(z.reportColumns.get(r.getReportid()+"_"+key.replace("_from", ""))!=null) {
					name = key.replace("_from", "");
				}

				if(!"pagenum".equals(name) 
						&& !"rowcount".equals(name)
						&& !"pagecount".equals(name)
						&& !"datacount".equals(name)
						&& !"query_terms".equals(name)
						&& !"otherwhere".equals(name)
						&& !"orderby".equals(name)
						&& !"orderby_pattern".equals(name)
						&& !"tableId".equals(name)
						&& !"info".equals(name)
						&& !"info_map".equals(name)
						&& !"zid".equals(name)){

					//获取字段链接符号
					String compare = z.reportColumns.get(r.getReportid()+"_"+name).getCompare();

					String value = bean.get(name);//根据参数名获取值
					if(z.isNull(compare) && z.isNotNull(value)) {
						sql.append(" and "+name+" = "+"'"+value+"'");
					}else {
						//拼接Where条件
						if("0".equals(compare) && z.isNotNull(value)) {
							sql.append(" and "+name+" = "+"'"+value+"'");
						}else if("1".equals(compare) && z.isNotNull(value)) {
							sql.append(" and "+name+" like "+"'%"+value+"%'");
						}else if("2".equals(compare) && z.isNotNull(value)) {
							sql.append(" and "+name+" in("+StringUtil.ListToSqlInArray(value)+")");
						}else if("3".equals(compare) && z.isNotNull(bean.get(name+"_from")) && z.isNotNull(bean.get(name+"_to"))) {
							String from_value = bean.get(name+"_from");//获取区间开始值
							String to_value = bean.get(name+"_to");//获取区间结束值
							//如果字段类型为日期，增加日期格式化处理在比较
							if("9".equals(z.reportColumns.get(r.getReportid()+"_"+name).getColumn_type())) {
								sql.append(" and (DATE_FORMAT("+name+",'%Y-%m-%d') >= '"+from_value+"'  and DATE_FORMAT("+name+",'%Y-%m-%d') <= '"+to_value+"' ) ");
							}else {
								sql.append(" and ("+name+" >= '"+from_value+"'  and "+name+" <= '"+to_value+"' ) ");
							}
						}
					}
				}
			}
		}

		//添加分页【Oracle】
		z_db db = z.dbsMap.get(r.getDbid());
		if(z.isNotNull(db) && "oracle".equals(db.getDb_type())) {
			String pagenum = bean.get("pagenum");
			String rowcount = bean.get("rowcount");
			if(!z.isNotNull(pagenum) || !z.isNotNull(rowcount)) {
				pagenum = "1";
				rowcount = z.sp.get("rowcount");
			}
			int pagenum_int = Integer.valueOf(pagenum);//页号
			int rowcount_int = Integer.valueOf(rowcount);//分页显示行数
			if(pagenum_int>0 && rowcount_int>0) {//页号和分布显示行数都必须大于0
				int row_num = (pagenum_int-1)*rowcount_int;//分页开始行号
				sql.append(" and rownum >= "+row_num+" and rownum<="+rowcount+"  ");
			}else {
				sql.append(" and rownum <= "+rowcount+"  ");
			}
			bean.put("pagenum", pagenum);
			bean.put("rowcount", rowcount);
		}

		//添加综合查询条件
		if(!"".equals(bean.get("query_terms")) && bean.get("query_terms")!=null) {
			String query_terms_sql = CreateQueryTerms(bean.get("query_terms"));
			if(!"".equals(query_terms_sql) && query_terms_sql!=null) {
				sql.append(" and "+query_terms_sql+" ");
			}
		}
		//添加其它条件
		if(!"".equals(bean.get("otherwhere")) && bean.get("otherwhere")!=null) {
			sql.append(" and "+bean.get("otherwhere")+" ");
		}

		//添加排序条件
		if(z.isNotNull(bean.get("orderby")) && z.isNotNull(bean.get("orderby_pattern"))) {
			//如果参数对象的orderby条件不为空，使用参数对象的条件
			sql.append(" order by "+bean.get("orderby")+" "+bean.get("orderby_pattern"));
		}


		//添加分页【MySQL】
		if(z.isNotNull(db) && "mysql".equals(db.getDb_type())) {
			String pagenum = bean.get("pagenum");
			String rowcount = bean.get("rowcount");
			if(!z.isNotNull(pagenum) || !z.isNotNull(rowcount)) {
				pagenum = "1";
				rowcount = z.sp.get("rowcount");
			}
			int pagenum_int = Integer.valueOf(pagenum);//页号
			int rowcount_int = Integer.valueOf(rowcount);//分页显示行数
			if(pagenum_int>0 && rowcount_int>0) {//页号和分布显示行数都必须大于0
				int row_num = (pagenum_int-1)*rowcount_int;//分页开始行号
				sql.append(" limit "+row_num+","+rowcount);
			}else {
				sql.append(" limit 0,"+rowcount);
			}
			bean.put("pagenum", pagenum);
			bean.put("rowcount", rowcount);
		}


		if(z.isNull(r.getDbid())) {
			return selectList(sql.toString());
		}else {
			return selectList(z.dbs.get(r.getDbid()),sql.toString());
		}

	}

	/**
	 * 获取代办任务列表
	 * @return
	 */
	public List<HashMap<String,String>> getWorkjobList(String userid) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" 	wj.NAME workjob_title, ");
		sql.append(" 	zuser.user_name, ");
		sql.append(" 	GROUP_CONCAT( zu.zid ) approvalusers, ");
		sql.append(" 	GROUP_CONCAT(zu.zid,wau.is_approval) approvalusers_and_isapproval, ");
		sql.append(" 	wj.number, ");
		sql.append(" 	wj.table_id, ");
		sql.append(" 	wj.biz_id, ");
		sql.append(" 	wj.user_id, ");
		sql.append(" 	wj.create_time, ");
		sql.append(" 	wj.zid workjobid, ");
		sql.append(" 	wjn.node_title nodeid  ");
		sql.append(" FROM ");
		sql.append(" 	z_workjob wj ");
		sql.append(" 	LEFT JOIN z_workjob_node wjn ON wj.now_node = wjn.zid ");
		sql.append(" 	left join z_workjob_node wjn2 on wj.zid = wjn2.pid ");
		sql.append(" 	LEFT JOIN z_workjob_approvaluser wau ON wjn2.zid = wau.pid ");
		sql.append(" 	LEFT JOIN z_user zu ON wau.userzid = zu.zid ");
		sql.append(" 	LEFT JOIN z_user zuser ON wj.user_id = zuser.zid  ");
		sql.append("      GROUP BY wj.zid ");
		z_user superuser = z.users_userid.get(z.sp.get("super_user"));
		sql.append(" having  approvalusers_and_isapproval  like '%"+userid+"0%'  or wj.user_id = '"+userid+"'  or '"+superuser.getZid()+"' = '"+userid+"' ");
		List<HashMap<String,String>> list = sqlSession.selectList("select", sql);
		return list;
	}



}
